# coding: utf-8 or # -*- coding: utf-8 -*-
# 提供卡信息查询接口
# 缺点1当query指定列时生成的json有问题
# 缺点2没法自定义增加json
# flask_jwt
import os
import json
import threading
from flask import Flask
from xml.dom import minidom
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy import (create_engine, MetaData, Column, Integer, String)
from sqlalchemy import func
from sqlalchemy.sql.expression import and_, or_
from sqlalchemy import Table, Column, Integer, String, Text, DateTime, MetaData, ForeignKey, Boolean, types, Numeric
from datetime import datetime, date
from sqlca import get_Session
from json_exa import ret_json,row3dict
from pos_check import pos_en
from card_app_log import app_log
Base = declarative_base()


class card_ticket_sum(Base):
    # 表的名字:
    __tablename__ = 'v_card_ticket_sum'
    # 表的结构:
    C_CARD_ID = Column(String(30), primary_key=True)
    C_GATHER_ID = Column(String(3))
    N_BALANCE = Column(Numeric(12, 2))
    N_CONSUME = Column(Numeric(12, 2))
    D_CON_BEGIN = Column(DateTime)
    D_CON_END = Column(DateTime)
    C_ACT_ID = Column(String(30))
    N_FILL_ALL = Column(Numeric(12, 2))
    C_SHOP_ID = Column(String(10))
    C_SAVE_TYPE = Column(String(2))
    c_gather_name = Column(String(20))


class card_card(Base):
    # 表的名字:
    __tablename__ = 'card_card'
    # 表的结构:
    C_CARD_NO = Column(String(30), primary_key=True)
    C_CARD_ID = Column(String(30))
    C_CARD_CLASS = Column(String(3))
    N_CARD_LEVEL = Column(Numeric(12, 2))
    C_CARDTYPE_ID = Column(String(30))
    C_CARDTYPE_NAME = Column(String(30))
    D_BEGIN_DATE = Column(DateTime)
    D_END_DATE = Column(DateTime)
    C_PARA = Column(String(30))
    C_LIMIT = Column(String(30))
    C_PASSWORD = Column(String(32))
    C_MSR = Column(String(50))
    C_VALID_FLAG = Column(String(2))
    C_STATUS = Column(String(2))
    C_SHOP_ID = Column(String(10))
# C_CARD_CHECK_SURE
# C_CARD_CHECK_FIGURE
# C_CARD_CHECK_CODE
    C_CUST_ID = Column(String(40))
#     C_SHOP_USE_ID
#     C_CARD_FIGURE
#     C_CERNO
#     N_CUR_MONEY
# N_ALL_MONEY
# N_RATE_MANAGER
# C_OWNER_ID
# N_DEPOSIT
# C_BANK_FLAG
# C_NAME
# C_CUST_NAME
# C_CARD_KIND_ID
# D_CHECK_DATE
# C_BJ_FLAG
# C_IC_TYPE
# N_RATE_HY
# N_RATE_CY
# N_ALL_SCORE
# N_CUR_SCORE
# N_ALL_POINT
# N_CUR_POINT
# N_SCORE_RATE
# N_SALE_MON
# N_CONSUME_MON
# N_SALECARD_JE
# N_PRIZE_NUM
# N_PRIZE_MONEY
# C_LMK_TYPE
# N_SCORE_VALID
# D_SCORE_VALID
# N_SCORE_VALID_ALL
# D_SCORE_VALID_END





def parse_track(as_track1, as_track2, as_track3):
    # print as_track1
    # print as_track2
    # print as_track3
    card_no = as_track2
    #正式
    # if 
    session = get_Session()()
    if card_no[0:5] == '62223':
        p = as_track2.find('=')
        if p > 0:
            as_cardid = as_track2[8:p]
        else:
            as_cardid = as_track2
        ls_msg = 'succeed'
        return {"code": "10", "error": ls_msg,"card_id":as_cardid} 
    elif card_no[0:5] == '62290':
        p = as_track3.find('=') + 20
        p1 = 8
        as_cardid = as_track3[p,p1]
        ls_msg = 'succeed'
        return {"code": "10", "error": ls_msg,"card_id":as_cardid} 
    elif card_no[0:5] == '62266':
        as_cardid = as_track2[0:16]
        ls_msg = 'succeed'
        return {"code": "10", "error": ls_msg,"card_id":as_cardid}
    elif card_no[0:5] == '62220':
        as_cardid = '58010100001508' 
        # as_track2[0:16]
        query= session.query(card_card).filter(card_card.C_CARD_ID==as_cardid).scalar()
        cardtype = query.C_CARDTYPE_ID
        ls_msg = 'succeed' 
        return {"code": "10", "error": ls_msg,"card_id":as_cardid,"cardtype":cardtype} 
    else:
        as_cardid = as_track2
        as_msr = as_track3
        
        if not isnumeric(as_msr):
            ls_msg = '卡加密串非法'+as_msr
            print ls_msg
            return {"code": "46", "error": ls_msg}
        print as_msr 
       
        query= session.query(func.count(1)).filter(card_card.C_CARD_ID==as_cardid).scalar()
        if query == 0:
            ls_msg = '卡号'+as_cardid+'不存在'
            print ls_msg
            session.close()
            return {"code": "41", "error": ls_msg} 
        query= session.query(card_card).filter(card_card.C_CARD_ID==as_cardid).scalar()
        cardtype = query.C_CARDTYPE_ID
        ll_count = session.execute("SELECT count(*) From card_property Where c_cardtype_id =:id",{"id":cardtype}).first()[0]
        if ll_count == 0:
            ls_msg = '卡类型不存在'
            print ls_msg
            session.close()
            return {"code": "42", "error": ls_msg}
        ls_msr = session.execute("SELECT c_msr,get_msr(:as_msr) from card_card where C_CARD_ID= :as_cardid",{"as_msr":as_msr,"as_cardid":as_cardid}).first()
        # if ls_msr[0] != ls_msr[1] and ls_msr[0] != '':
        #     ls_msg = '卡加密串非法'
        #     session.close()
        #     return {"code": "42", "error": ls_msg}
        session.close()
        ls_msg = 'succeed'
        return {"code": "10", "error": ls_msg,"card_id":as_cardid,"cardtype":cardtype} 



# session = get_Session()()
# as_msr = '34242213'
# as_cardid = '7181015571'
# ls_msr = session.execute("SELECT c_msr,get_msr(:as_msr) from card_card where C_CARD_ID= :as_cardid ",{"as_msr":as_msr,"as_cardid":as_cardid}).first()
# print ls_msr

# app = Flask(__name__)

# @app.route('/card_info/<card_no>',methods=['GET', 'POST'])
# def get_cardinfo(card_no):
# 	# return 'card %s'%card_no
#     return get_card_info(card_no)

# ---测试---http://127.0.0.1/card_info/
# source = 'req_type=query&req_gather=1&Key=rtedfg435dgdfg&req_data={"posno": "1233","cashier": "001",\
#     "Track1": "32424234234",\
#     "Track2": "qweqe324223424",\
#     "track3": "34242xsdfsf" }'


# list = source.split("&")
# # print list
# dic={} 
# for i in list:
#     li = i.split("=")
#     dic[li[0]] = li[1]

# print dic
# di = eval(dic["req_data"])
# print di["posno"]

def isnumeric(s):
  '''returns True if string s is numeric'''
  return all(c in "0123456789.+-" for c in s)


def get_card_info(card_no):
    print card_no
    app_log(card_no,'input')
    json_list = card_no.split('&')
    dt = {}
    for lst in json_list:
        li = lst.split('=')
        dt[li[0]] = li[1]
    di = eval(dt["req_data"])
    ls_posno = di["posno"]
    ret_pos = pos_en(ls_posno)
    if ret_pos['code'] != '10':
        return json.dumps(ret_pos, ensure_ascii=False)
    ls_shop = ret_pos["C_SHOP_ID"]
    session = get_Session()()
    card_info = parse_track(di['track1'],di['track2'],di['track3'])
    if card_info['code'] != '10':
        return json.dumps(card_info, ensure_ascii=False)
    card_id = card_info['card_id']
    ll_count = session.execute("SELECT count(*) From card_card Where c_card_id =:id",{"id":card_id}).first()[0]
    if ll_count == 0:
        ls_msg = '无此卡号'
        print ls_msg
        return json.dumps({"code": "45", "error": ls_msg},ensure_ascii=False)
    vmcs = session.query(card_card).filter(card_card.C_CARD_ID==card_id).first()
    print  vmcs.C_VALID_FLAG
    if vmcs.C_VALID_FLAG != '1':
        ls_msg = '卡无效'
        print ls_msg
        return json.dumps({"code": "45", "error": ls_msg},ensure_ascii=False)
    vmcs = session.query(card_card).filter(card_card.C_CARD_ID==card_id).first()
    print vmcs.D_END_DATE
    dt = session.execute(
        "select to_char(sysdate - 1,'yyyy-mm-dd hh:mm:ss') from DUAL").first()[0]
    if str(vmcs.D_END_DATE) < dt:
        ls_msg = '卡已过有效期'
        print ls_msg
        return json.dumps({"code": "46", "error": ls_msg})
    dt_time = session.execute(
        "select sysdate  from DUAL").first()[0]
    # , card_ticket_sum.C_SHOP_ID = ls_shop #卡不能在此门店使用
    print dt_time
    query = session.query(card_ticket_sum).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.D_CON_END > dt_time ).all()
    card_balance =  ret_json(query)
    ls_msg = 'succeed'
    return  json.dumps({"code": "10", "error": ls_msg,"card_no":card_id,"data":card_balance},ensure_ascii=False)



# session = get_Session()()
# print session
# # vmcs = session.query(card_ticket_sum.C_CARD_ID,card_ticket_sum.C_GATHER_ID,card_ticket_sum.C_SHOP_ID).filter(card_ticket_sum.C_CARD_ID==card_no).all()
# vmcs = session.query(card_ticket_sum).all()
# # print json.dumps(vmcs, cls=AlchemyEncoder)
# print vmcs


# as_cardid = '7131004280'
# query= session.query(card_card).filter(card_card.C_CARD_ID==as_cardid).scalar()
# print  query.C_CARDTYPE_ID
# cardtype = query.C_CARDTYPE_ID
# dt = session.execute("SELECT count(*) From card_property Where c_cardtype_id =:id",{"id":cardtype}).first()[0]
# print dt


# li = get_tolist(source,'&')

# print (li)
# for l in li:
#     print l


# ---测试
# source = 'sdfsfd,你好啊,说好的分手费'
# a = get_token(source,',')
# print a[0]
# print a[1]

# print b
# print source

# # source[:len(source)-(p+len(separator) )]
# source = 'sdfs,sdf'
# p = 5
# print source[-4:]

# http://127.0.0.1/card_info/req_type=query&req_gather=1&Key=rtedfg435dgdfg&req_data={
#     "posno": "1233",
#     "cashier": "001",
#     "Track1": "32424234234",
#     "Track2": "qweqe324223424",
#     "track3": "34242xsdfsf"
# }

# if __name__ == '__main__':
#       app.run()


# if __name__ == '__main__':
# 	dbsession=get_Session()
# 	session = dbsession()
# 	get_card_info('7183002060',session)


# @app.route('/user/<username>')
# def show_user_profile(username):
#     # show the user profile for that user
#     return 'User %s' % username

# session = get_Session()()
# card_id = '7183002060'
# vmcs = session.query(card_card).filter(card_card.C_CARD_ID==card_id).all()
# print vmcs.D_END_DATE
# query = session.query(card_ticket_sum).filter(card_ticket_sum.C_CARD_ID==card_id and card_ticket_sum.D_END_DATE > sysdate ).all()
# card_balance =  ret_json(query)
# ls_msg = '成功'
# print  json.dumps({"code": "10", "error": ls_msg,"card_no":card_id,"data":card_balance},ensure_ascii=False)

# as_msr = '231231'
# as_cardid = '7183002060'
# ls_msr = session.execute("SELECT c_msr,get_msr(:as_msr) from card_card where c_card_id = :as_cardid ",{"as_msr":'232323',"as_cardid":as_cardid}).first()
# print ls_msr
# dt = session.execute(
#         "select to_char(sysdate - 1,'yyyy-mm-dd hh:mm:ss') from DUAL").first()[0]
# print  str(vmcs.D_END_DAas_msr = '231231'TE) < dt 



# print pattern.match(str)
# str = 's222sss'
# import re
# print re.search('.*[0-9]+.*', str)

import re 
# def hasNumbers(inputString):
#     return bool(re.search('.*[0-9]+.*', inputString))


# print hasNumbers(str)
# a = '34342a'
# b= '1111'
# c = 'fgh'
# import re
# print "a", re.match(r"d+$", a) and True or False
# print "b", re.match(r"d+$", b) and True or False
# print "c", re.match(r"d+$", c) and True or False


# print re.match(r'[+-]?d+$', '1234′)


# import re
# x = '11'
# print re.search('.*[0-9]+.*', x)


''' isnumeric.py
test a numeric string s if it's usable
for int(s) or float(s)
'''



# test module ...
if __name__ == '__main__':
  print(isnumeric('123a'))   # True
  print(isnumeric('-123.45')) # True
  print(isnumeric('+3.14'))  # True
  print(isnumeric('$asd.95'))  # False



# session = get_Session()()
# card_id = '6222020200039771'
# vmcs = session.query(card_card).filter(card_card.C_CARD_ID==card_id).first()
# print vmcs
# print  vmcs.C_VALID_FLAG
# if vmcs.C_VALID_FLAG != '1':
#     ls_msg = '卡无效'
# print ls_msg

# vmcs = session.query(card_card).filter(card_card.C_CARD_ID==card_id).first()
# print vmcs.D_END_DATE
# dt = session.execute(
#         "select to_char(sysdate - 1,'yyyy-mm-dd hh:mm:ss') from DUAL").first()[0]
# if str(vmcs.D_END_DATE) < dt:
#     ls_msg = '卡已过有效期'
#     print ls_msg

